package com.twfpro.sql;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;

import org.json.JSONArray;
import org.json.JSONObject;

public class SqlRequest
{
	private String sqlUrl;
	private String userName;
	private String passWord;

	/**
	 * 数据库类
	 * 
	 * @param sqlUrl
	 *            数据库地址
	 * @param userName
	 *            账号
	 * @param passWord
	 *            密码
	 */
	public SqlRequest(String sqlUrl, String userName, String passWord)
	{
		this.sqlUrl = sqlUrl;
		this.userName = userName;
		this.passWord = passWord;
	}

	/**
	 * 查询
	 * 
	 * @param lib
	 *            库名
	 * @param sqlQue
	 *            查询语句
	 * @return 查询结果json
	 */
	public JSONArray select(String lib, String sqlQue)
	{
		JSONArray result = new JSONArray();
		String mysqlUrl = sqlUrl + lib
				+ "?characterEncoding=utf8&serverTimezone=Asia/Shanghai&zeroDateTimeBehavior=ROUND";
		try (Connection connection = DriverManager.getConnection(mysqlUrl, userName, passWord))
		{
			// System.out.println(sql);
			PreparedStatement statement = connection.prepareStatement(sqlQue);
			// statement.setLong(1, uid);
			ResultSet rs = statement.executeQuery();
			ResultSetMetaData rsmd = rs.getMetaData();
			while (rs.next())
			{
				JSONObject data = new JSONObject();
				for (int i = 1; i < rsmd.getColumnCount() + 1; i++)
				{
					String columnName = rsmd.getColumnName(i);
					data.put(columnName, rs.getString(columnName));
				}
				result.put(data);
			}
			connection.close();
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}
		return result;
	}

	/**
	 * 更新方法
	 * 
	 * @param lib
	 *            库名
	 * @param sqlQue
	 *            sql语句
	 * @return 是否执行成功
	 */
	public boolean update(String lib, String sqlQue)
	{
		String mysqlUrl = sqlUrl + lib
				+ "?characterEncoding=utf8&serverTimezone=Asia/Shanghai&zeroDateTimeBehavior=ROUND";
		try (Connection connection = DriverManager.getConnection(mysqlUrl, userName, passWord))
		{
			PreparedStatement statement = connection.prepareStatement(sqlQue);
			statement.execute();
			connection.close();
		}
		catch (SQLException e)
		{
			e.printStackTrace();
			return false;
		}
		return true;
	}

}
